﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Windows.Forms;
using System.IO;
using OracleDbTool;
using HtmlAgilityPack;

namespace hkjc
{
    public partial class Form1 : Form
    {
        private int m_iTickCount;
        string m_sRaceDate;
        string m_sVenue;
        int m_iRaceCount;
        private static String SHARED_LOCK = "shared_lock";


        string[] m_wp_LastBatchNo =new string[20];
        string[] m_qinpla_LastBatchNo = new string[20];
        string[] m_double_LastBatchNo = new string[20];
        string[] m_inv_lastbachno = new string[20];

        List<DateTime> m_lstRaceTime;

        public Form1()
        {
            InitializeComponent();
            OrclFunc.OpenLocalConnection("Data Source=XE;User ID=gxl;Password=andy;");
        }

        private void LogMsg(string msg, int mode = 0)
        {
            if (msg.Contains("No update"))
                return;

            Application.DoEvents();
            if (mode == 0)
            {
                listBox1.Items.Add(DateTime.Now.ToString("HH:mm:ss") + ":" + msg);
                if (listBox1.Items.Count >= 1000)
                {
                    //StringBuilder sb = new StringBuilder();
                    //for (int i = 0; i < listBox1.Items.Count; i++)
                    //    sb.AppendLine(listBox1.Items[i].ToString());

                    //File.WriteAllText("Log"+DateTime.Now.ToString("HHmmss")+".txt", sb.ToString());
                    //Application.DoEvents();
                    listBox1.Items.Clear();
                }
            }
        }

        private void GetAllRaceInfo(bool fJustGetRaceTime = true)
        {
            if (fJustGetRaceTime)
            {
                string sql = "select raceno,racetime from t_raceinfo where racedate = '{0}' order by raceno ";
                sql = string.Format(sql, m_sRaceDate);
                DataSet ds = OrclFunc.QueryBySqlLocal(sql);
                foreach (DataRow r in ds.Tables[0].Rows)
                    if (r["racetime"] != DBNull.Value)
                        m_lstRaceTime.Add(Convert.ToDateTime(r["racetime"]));
                    else
                        listBox1.Items.Add("GetAllRaceInfo Error:Race time of race #" + r["raceno"].ToString() + " is not valid time");

                return;
            }
            for (int i = 1; i <= m_iRaceCount; i++)
                GetRaceInfo2(m_sRaceDate,m_sVenue, i.ToString());
        }

        void GetRaceInfo2(string racedate, string venue, string raceno)
        {
            //删除已有数据
            OrclFunc.ExecuteNonQueryLocal(string.Format("delete from t_raceinfo where racedate='{0}' and venue='{1}' and raceno='{2}'",racedate,venue,raceno));
            OrclFunc.ExecuteNonQueryLocal(string.Format("delete from t_racehorses where racedate='{0}' and venue='{1}' and raceno='{2}'", racedate, venue, raceno));
            //场次数据
            string sqlpattern = @"insert into t_raceinfo(racedate,venue,raceno) values ('{0}','{1}','{2}')";

            string sql = string.Format(sqlpattern, racedate, venue, raceno);
            OrclFunc.ExecuteNonQueryLocal(sql);
            //马匹数据
            string url = "http://racing.hkjc.com/racing/Info/Meeting/RaceCard/Chinese/Local/{0}/{1}/{2}";
            url = string.Format(url, racedate, venue, raceno);

            string html = WebFunctions.OpenUrl(url);
            HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
            doc.LoadHtml(html);
            //doc.Load("test.html");

            string table = doc.DocumentNode.SelectSingleNode(@"//table[@class=""draggable hiddenable""]").OuterHtml;
            //File.WriteAllText("table.txt", table);
            doc.LoadHtml(table);

            sqlpattern = @"insert into t_racehorses(racedate,venue,raceno,horseno,lastsixruns, 
                                    horsename,weight,jockey,drawno,trainer) values
                                ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')";
            List<string> sqls = new List<string>{};

            foreach (HtmlAgilityPack.HtmlNode node in doc.DocumentNode.SelectNodes("/table/tr[position()>1]"))
            {
                //if 
                sqls.Add(string.Format(sqlpattern, racedate, venue, raceno,
                                        node.SelectSingleNode("./td[1]").InnerText.Trim(), //6 runs
                                        node.SelectSingleNode("./td[2]").InnerText.Trim(), //6 runs
                                        node.SelectSingleNode("./td[4]").InnerText.Trim(), //horsename
                                        node.SelectSingleNode("./td[6]").InnerText.Trim(), //weight
                                        node.SelectSingleNode("./td[7]").InnerText.Trim(), //jockey
                                        node.SelectSingleNode("./td[9]").InnerText.Trim(), //drawno
                                        node.SelectSingleNode("./td[10]").InnerText.Trim()//, //trainer 
                                        //node.SelectSingleNode("./td[19]").InnerText, //priority
                                        //node.SelectSingleNode("./td[20]").InnerText  //gear
                                        ));
            }
            OracleDbTool.OrclFunc.ExecNonQueryTransLocal(sqls.ToArray(), sqls.Count);
        }

        private void GetRaceInfo(string racedate, string venue, string raceno)
        {
            // sample 20140615 ST 2
            string url = "http://racing.hkjc.com/racing/Info/Meeting/RaceCard/Chinese/Local/{0}/{1}/{2}";
            url = string.Format(url, racedate, venue, raceno);

            List<string[]> rows = new List<string[]> { };

            EasyScraper es = EasyScraper.LoadFromFile("raceinfo.xml");

            TResult rt = WebFunctions.RunEasyScraperOnUrl(url, es, ref rows);
            if (rt.ErrMsg != string.Empty)
                LogMsg(rt.ErrMsg);
            try
            {
                m_lstRaceTime.Add(DateTime.ParseExact(racedate + " " + rt.returnvalue, "yyyyMMdd HH:mm", null));
            }
            catch (Exception e1)
            {
                LogMsg("Get racetime from page failed(" + e1.Message + ") ,set to default time");
                m_lstRaceTime.Add(DateTime.Now.AddDays(1).Date);
            }
            string sqlpattern = @"insert into t_raceinfo(racedate,venue,raceno,racetime) values ('{0}','{1}','{2}',to_date('{3}','yyyymmdd hh24:mi'))";
            string sql = string.Format(sqlpattern, racedate, venue, raceno, racedate + " " + rt.returnvalue);
            OrclFunc.ExecuteNonQueryLocal(sql);

            //write to db
            int startrow = 1;
            sqlpattern = @"insert into t_racehorses(racedate,venue,raceno,horseno,lastsixruns, 
                                    horsename,weight,jockey,drawno,trainer,priority,gear) values
                                ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')";
            sql = string.Empty;
            for (int i = startrow; i < rows.Count; i++)
            {

                sql = string.Format(sqlpattern, racedate, venue, raceno, 
                    rows.ElementAt(i)[0].Replace("'","''"),
                    rows.ElementAt(i)[1].Replace("'", "''"),
                    rows.ElementAt(i)[2].Replace("'", "''"),
                    rows.ElementAt(i)[3].Replace("'", "''"),
                    rows.ElementAt(i)[4].Replace("'", "''"),
                    rows.ElementAt(i)[5].Replace("'", "''"),
                    rows.ElementAt(i)[6].Replace("'", "''"),
                    rows.ElementAt(i)[7].Replace("'", "''"),
                    rows.ElementAt(i)[8].Replace("'", "''"));

                lock (SHARED_LOCK)
                {
                    OrclFunc.ExecuteNonQueryLocal(sql);
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            m_sRaceDate = dtPicker1.Text;
            m_sVenue = cbVenue.Text;
            m_iRaceCount = int.Parse(cbRaceCount.Text);
            if (m_lstRaceTime != null)
                m_lstRaceTime = null;
            m_lstRaceTime = new List<DateTime> { };
            //GetAllRaceInfo(false); //initialze all
            GetAllRaceInfo(true); //initialze just racetime
            //MessageBox.Show(WebFunctions.GetXmlContent("http://bet.hkjc.com/racing/getXML.aspx?type=jcbwracing_full&date=15-06-2014&venue=ST&raceno=3&pool=qin&tag=QIN/RACE"));            
        }
        #region 更新各彩池数据批号(最后及其次)

        /// <summary>
        /// 初始化批号表
        /// </summary>
        /// <param name="racedate">比赛日期</param>
        /// <param name="venue">场地</param>
        /// <param name="racecount">场数</param>
        void InitBatchNo(string racedate, string venue,int racecount)
        {
            string sql = @"insert into T_LATEST_BATCHNO(racedate,venue,raceno) values('{0}','{1}','{2}')";
            for(int i = 1;i <= racecount;i++)
                lock (SHARED_LOCK)
                {
                    OrclFunc.ExecuteNonQueryLocal(string.Format(sql,racedate, venue, i.ToString()));
                }
        }

        /// <summary>
        /// 更新各彩池数据批号
        /// </summary>
        /// <param name="racedate">比赛日期</param>
        /// <param name="venue">场地</param>
        /// <param name="raceno">场次</param>
        /// <param name="batchno_name_1st">最后批号数据库字段名称</param>
        /// <param name="batchno_name_2nd">其次批号数据库字段名称</param>
        /// <param name="batchno">最新批号</param>
        void UpdateBatchNo(string racedate, string venue, string raceno, string batchno_name_1st, string batchno_name_2nd, string batchno)
        {
            string sql =
@"
update T_LATEST_BATCHNO t
set t.{0}='{2}',t.{1}=t.{0}
where 
  t.racedate='{3}'
  and  t.venue='{4}'
  and t.raceno='{5}'";

            lock (SHARED_LOCK)
            {
                OrclFunc.ExecuteNonQueryLocal(string.Format(sql,batchno_name_1st,batchno_name_2nd,batchno,racedate,venue,raceno));
            }
        }

        void UpdateSortedWin(string racedate, string venue, string raceno, string batchno)
        {
            string sql = @"select rpad(lpad(w.horseno,2) || '. ' || h.horsename,18) || w.w_odds OddsInfo
  from t_winplace_odds w, t_racehorses h
 where w.racedate = '{0}'
   and w.venue = '{1}'
   and w.raceno = '{2}'
   and w.batchno = '{3}'
   and h.racedate = '{0}'
   and h.venue = '{1}'
   and h.raceno = '{2}'
   and h.horseno = w.horseno
 order by w.w_odds asc";

            DataTable dt = null;
            lock (SHARED_LOCK)
            {
                dt = OrclFunc.QueryBySqlLocal(string.Format(sql, racedate, venue, raceno, batchno)).Tables[0];
            }

            int sortedCount = (dt.Rows.Count >= 8) ? 8 : dt.Rows.Count;
            //填充8个独赢赔率

            List<string> ls = new List<string> { };
            for (int i = 0; i < sortedCount; i++)
                ls.Add(dt.Rows[i]["OddsInfo"].ToString());

            for (int j = sortedCount; j < 8; j++)
                ls.Add("");

            ls.Add(racedate);
            ls.Add(venue);
            ls.Add(raceno);

            sql = @"update T_LATEST_BATCHNO set w1='{0}',w2='{1}',w3='{2}',w4='{3}',w5='{4}',w6='{5}',w7='{6}',w8='{7}' where racedate='{8}' and venue='{9}' and raceno='{10}'";

            lock (SHARED_LOCK)
            {
                OrclFunc.ExecuteNonQueryLocal(string.Format(sql, ls.ToArray()));
            }
        }

        bool CheckBatchNoExists(string racedate, string venue, string raceno, string batchno_name_1st, string batchno)
        {            

            string sql = @"select count(*) from T_LATEST_BATCHNO where racedate='{0}' and venue='{1}' and raceno='{2}' and {3}='{4}'";

            int i = 0;
            lock (SHARED_LOCK)
            {
                i = OrclFunc.ExecuteScalarIntLocal(string.Format(sql, racedate, venue, raceno, batchno_name_1st, batchno));
            }

            return (i>0);
        }

        #endregion

        //return yyyyMMdd hh24:mi:ss
        private string  GetRefreshTimeTag(string sOdds)
        {
            string ret = string.Empty;
            //case 1:start with [hh24miss@@@]
            //case 2:for investment xml data,[updateTime="hh24:mi:ss"]
            int i = sOdds.IndexOf("@@@");
            int j = sOdds.IndexOf("updateTime=");
            if (i > 0)
            {
                ret = sOdds.Substring(0, i);
                //ret = ret.Substring(0, 2) + ":" + ret.Substring(2, 2) + ":" + ret.Substring(4, 2);
            }
            else if (j > 0)
                ret = sOdds.Substring(j + 12, 8).Replace(":","");

            if (!string.IsNullOrEmpty(ret))
            {
                if (DateTime.Now.ToString("yyyyMMdd") == m_sRaceDate && string.Compare(ret, "23:30:00") > 0)
                    ret = DateTime.Now.AddDays(-1).ToString("MMdd") + ret;
                else
                    ret = DateTime.Now.ToString("MMdd") + ret;
            }
            
            return ret;
        }

        /*<?xml version="1.0"?>
            <OUT>152153@@@WIN;
         * 1=7.1=0;2=13=0;3=1.9=1;4=12=0;5=4.4=0;6=6.0=0#PLA;
         * 1=3.9=0;2=3.3=0;3=1.0=1;4=4.8=0;5=4.6=0;6=2.9=0@@@WIN;
         * 1=19=0;2=5.7=0;3=4.3=1;4=28=0;5=60=0;6=5.4=0;7=68=0;8=9.6=0;9=28=0;10=10=0;11=13=0;12=5.0=0#PLA;
         * 1=3.6=0;2=2.1=0;3=2.1=0;4=8.3=0;5=16=0;6=1.9=0;7=15=0;8=3.0=0;9=5.5=0;10=4.1=0;11=4.6=0;12=1.4=1@@@WIN;
         * 1=5.1=0;2=38=0;3=16=0;4=67=0;5=12=0;6=20=0;7=6.9=0;8=9.4=0;9=5.6=0;10=23=0;11=17=0;12=3.8=1#PLA;
         * 1=2.4=0;2=9.3=0;3=2.5=0;4=13=0;5=4.2=0;6=5.3=0;7=3.0=0;8=5.5=0;9=1.7=0;10=6.7=0;11=5.7=0;12=1.2=1@@@WIN;
         * 1=1.7=1;2=10=0;3=21=0;4=10=0;5=9.5=0;6=12=0;7=24=0;8=44=0;9=13=0;10=67=0;11=30=0;12=29=0#PLA;
         * 1=1.1=1;2=1.8=0;3=3.7=0;4=2.9=0;5=2.6=0;6=4.7=0;7=6.4=0;8=8.0=0;9=3.5=0;10=11=0;11=6.1=0;12=6.0=0@@@WIN;
         * 1=14=0;2=7.5=0;3=9.8=0;4=6.9=0;5=13=0;6=18=0;7=67=0;8=11=0;9=14=0;10=7.1=0;11=5.0=1;12=25=0;13=33=0;14=18=0#PLA;
         * 1=4.6=0;2=3.5=0;3=3.7=0;4=2.9=0;5=3.8=0;6=3.6=0;7=15=0;8=2.1=1;9=3.0=0;10=3.8=0;11=2.7=0;12=7.5=0;13=5.9=0;14=5.0=0@@@WIN;
         * 1=3.4=0;2=103=0;3=9.0=0;4=40=0;5=13=0;6=31=0;7=17=0;8=20=0;9=7.6=0;10=82=0;11=3.3=1;12=23=0;13=43=0;14=18=0#PLA;
         * 1=1.9=0;2=23=0;3=2.2=0;4=16=0;5=5.6=0;6=6.2=0;7=9.8=0;8=5.0=0;9=3.0=0;10=13=0;11=1.0=1;12=7.0=0;13=10=0;14=6.2=0@@@WIN;
         * 1=7.1=0;2=28=0;3=4.8=0;4=34=0;5=45=0;6=23=0;7=19=0;8=8.2=0;9=34=0;10=11=0;11=9.8=0;12=23=0;13=4.3=1;14=13=0#PLA;
         * 1=2.5=0;2=6.4=0;3=2.8=0;4=7.6=0;5=9.8=0;6=3.5=0;7=5.0=0;8=2.7=0;9=8.1=0;10=4.2=0;11=5.4=0;12=5.4=0;13=2.2=0;14=2.1=1@@@WIN;
         * 1=9.9=0;2=8.7=0;3=8.0=0;4=22=0;5=23=0;6=6.2=0;7=16=0;8=8.4=0;9=12=0;10=4.6=1;11=25=0;12=14=0;13=38=0;14=52=0#PLA;
         * 1=2.7=0;2=1.7=1;3=4.1=0;4=10=0;5=8.3=0;6=3.0=0;7=5.3=0;8=2.2=0;9=4.2=0;10=2.0=0;11=6.4=0;12=5.2=0;13=8.1=0;14=11=0@@@WIN;
         * 1=13=0;2=6.7=0;3=34=0;4=13=0;5=24=0;6=28=0;7=4.5=1;8=30=0;9=25=0;10=4.8=0;11=11=0;12=9.5=0;13=69=0;14=9.5=0#PLA;*/
        private TResult WriteWpOddsToDB(string sOdds,int raceno_start,int raceno_end,string inv_batchno)
        {
            string batchno = GetRefreshTimeTag(sOdds);
            TResult rt = new TResult();
            DateTime dt;

            if (!DateTime.TryParseExact(batchno, "MMddHHmmss", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out dt))
            {
                //LogMsg("WriteWpOddsToDB->GetRefreshTimeTag returned null,sOdds=" + sOdds.Substring(0, sOdds.Length>20?20:sOdds.Length));
                rt.ErrMsg = "WriteWpOddsToDB->GetRefreshTimeTag returned null,sOdds=" + sOdds.Substring(0, sOdds.Length > 20 ? 20 : sOdds.Length);
                rt.Success = false;
                return rt;
            }

            if (CheckBatchNoExists(m_sRaceDate,m_sVenue,raceno_start.ToString(),"win_1st_batchno",batchno))
            {
                rt.Success = false;
                rt.ErrMsg = "No update for winplace odds of raceno #" + raceno_start.ToString() + " at this time.";
                return rt;
            }                  

            //need to update odds           
            string sql = string.Empty;
            string sqlpattern = LoadSql("SQL_INS_WINPLACE_ODDS");

            string[] ps = sOdds.Split(new string[] { "@@@WIN;","#PLA;" }, StringSplitOptions.None);
            //ps[1] ps[2] -> raceno 1...
            string[] ps1;
            string[] ps2;
            int raceno = raceno_start;

            int i;
            try
            {
                for (i = 1; i <= ps.GetUpperBound(0) && raceno <= raceno_end; i++)
                {
                    ps1 = ps[i].Split(new string[] { ";", "=" }, StringSplitOptions.None);
                    ps2 = ps[i + 1].Split(new string[] { ";", "=" }, StringSplitOptions.None);

                    // ps1[0],ps1[1],ps1[2] => horseno,win_odds,hotflag
                    // ps2[0],ps2[1],ps2[2] => horseno,place_odds,hotflag
                    for (int j = 0; j <= ps1.GetUpperBound(0); j++)
                    {
                        if (ps1[j + 1].ToUpper() == "SCR" || ps2[j + 1].ToUpper() == "SCR")
                        {
                            ps1[j + 1] = "0";
                            ps2[j + 1] = "0";
                        }
                        sql = string.Format(sqlpattern, batchno, m_sRaceDate, m_sVenue, raceno.ToString(), ps1[j], ps1[j + 1], ps2[j + 1], DateTime.Now.ToString("yyyyMMdd HH:mm:ss"), "",inv_batchno);
                        lock (SHARED_LOCK)
                        {
                            if (ps1[j+1]!="0" && ps2[j+1]!="0")
                                OrclFunc.ExecuteNonQueryLocal(sql);
                        }
                        j++;
                        j++;
                    }
                    //LogMsg("raceno=" + raceno + " winplace odds saved");

                    i++;
                    raceno++;
                }
            }
            catch (Exception e1)
            {
                rt.ErrMsg = "WriteWpOddsToDB error: raceno=" + raceno_start.ToString() +" msg=" + e1.Message;
                rt.Success = false;
            }

            m_wp_LastBatchNo[raceno_start] = batchno;
            return rt;
        }

        /*<OUT>123649@@@;
         * 1-2=49=0;1-3=33=0;2-3=435=0;1-4=253=0;2-4=217=0;3-4=338=0;1-5=27=0;2-5=106=0;3-5=135=0;4-5=203=0;1-6=48=0;2-6=358=0;3-6=117=0;4-6=304
         */
        private TResult WriteQpOddsToDB(string sOdds_qin,string sOdds_qpl,int raceno,string inv_batch_no,string wp_batchno)
        {
            //List<string> sqls = new List<string> { };  
            TResult rt = new TResult();
            string batchno = GetRefreshTimeTag(sOdds_qin);
            DateTime dt;

            if (!DateTime.TryParseExact(batchno, "MMddHHmmss", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out dt))
            {
                //LogMsg("WriteQpOddsToDB->GetRefreshTimeTag returned null,sOdds=" + sOdds_qin.Substring(0, sOdds_qin.Length>20?20:sOdds_qin.Length));
                rt.ErrMsg = "WriteQpOddsToDB->GetRefreshTimeTag returned null,sOdds=" + sOdds_qin.Substring(0, sOdds_qin.Length > 20 ? 20 : sOdds_qin.Length);
                rt.Success = false;
                return rt;
            }
            
            int i = 0;

            if (CheckBatchNoExists(m_sRaceDate, m_sVenue, raceno.ToString(), "qpl_1st_batchno", batchno))
            {
                rt.Success = false;
                rt.ErrMsg = "No update for qpl odds of raceno #" + raceno.ToString() + " at this time.";
                return rt;
            }

            string[] sqls = new string[100];
            string sql = string.Empty;
            string sqlpattern = LoadSql("SQL_INS_QINPLA_ODDS");

            string[] ps_qin = sOdds_qin.Split(new string[] { "@@@;" }, StringSplitOptions.None);
            string[] ps1_qin = ps_qin[1].Split(new string[] {";", "=" }, StringSplitOptions.None);

            //如果sOdds_qpl没数据，则赋值为sOdds_qin
            if (sOdds_qpl == "@@@")
                sOdds_qpl = sOdds_qin;

            string[] ps_qpl = sOdds_qpl.Split(new string[] { "@@@;" }, StringSplitOptions.None);
            string[] ps1_qpl = ps_qpl[1].Split(new string[] {";", "=" }, StringSplitOptions.None);

            
            int sqlcount = 0;
            
            //ps1[0] => horseno1-horseno2  ;  ps1[1] => odds ;ps1[2] => hotflag
            try
            {
                for (i = 0; i <= ps1_qin.GetUpperBound(0); i++)
                {
                    string[] ps2 = ps1_qin[i].Split(new string[] { "-" }, StringSplitOptions.None);//SCR
                    if (ps1_qin[i + 1].ToUpper() != "SCR" && ps1_qpl[i + 1].ToUpper() != "SCR")
                    {
                        sqls[sqlcount] = string.Format(sqlpattern, batchno, m_sRaceDate, m_sVenue, raceno.ToString(), ps2[0], ps2[1], ps1_qin[i + 1], ps1_qpl[i + 1], DateTime.Now.ToString("yyyyMMdd HH:mm:ss"), "", inv_batch_no, wp_batchno);
                        sqlcount++;
                    }

                    i++;
                    i++;                    
                }
                lock (SHARED_LOCK)
                {
                    OrclFunc.ExecNonQueryTransLocal(sqls, sqlcount);
                }                
            }
            catch (Exception e1)
            {
                rt.ErrMsg = "WriteQpOddsToDB error: raceno=" + raceno.ToString() + " msg=" + e1.Message;
                rt.Success = false;
            }
            //LogMsg("raceno=" + raceno + " q and qplace odds saved");
            return rt;
        }

        private TResult WriteInvestmentStatToDB(int raceno)
        {
            TResult rt = new TResult();
            string investment_url = "http://bet.hkjc.com/racing/getXML.aspx?type=pooltot&date={0}&venue={1}&raceno={2}";

            string racedate = m_sRaceDate.Substring(6) + "-" + m_sRaceDate.Substring(4, 2) + "-" + m_sRaceDate.Substring(0, 4);

            string batchno = string.Empty;
            //string ss = LoadSql("SQL_INS_INVESTMENTSTAT");
            try
            {
                string sql = WebFunctions.GetInvestmentStatSql(string.Format(investment_url, racedate, m_sVenue, raceno.ToString()), LoadSql("SQL_INS_INVESTMENTSTAT"), m_sRaceDate, m_sVenue, raceno.ToString(), out batchno);
                if (string.IsNullOrEmpty(sql) || string.IsNullOrEmpty(batchno))
                {
                    rt.Success = false;
                    if (Program.LastErrorMsg != string.Empty)
                        rt.ErrMsg = Program.LastErrorMsg;
                    else
                        rt.ErrMsg = "No update for investmentstat of race #" + raceno.ToString() + " currently";
                    return rt;
                }
                //check if exists
                if (CheckBatchNoExists(m_sRaceDate, m_sVenue, raceno.ToString(), "inv_1st_batchno", batchno))
                {
                    rt.Success = false;
                    rt.ErrMsg = "No update for investment stat.";
                    return rt;
                }                    
                
                lock (SHARED_LOCK)
                {
                    OrclFunc.ExecuteNonQueryLocal(sql);
                }
            }
            catch (Exception e1)
            {
                rt.ErrMsg = "WriteInvestmentStatToDB error: raceno=" + raceno.ToString() + " msg=" + e1.Message;
                rt.Success = false;
            }

            m_inv_lastbachno[raceno] = batchno;
            return rt;                
        }

        private void WriteAllInvestmentStatToDB(string batchno)
        {
            string investment_url = "http://bet.hkjc.com/racing/getXML.aspx?type=pooltot&date={0}&venue={1}&raceno={2}";

            string[] sqls = new string[20];
            int sqlcount = 0;

            string racedate = m_sRaceDate.Substring(6) + "-" + m_sRaceDate.Substring(4, 2) + "-" + m_sRaceDate.Substring(0, 4);
            for (int i = 1; i <= m_iRaceCount; i++)
            {
                sqls[sqlcount++] = WebFunctions.GetInvestmentStatSql(string.Format(investment_url, racedate, m_sVenue, i), LoadSql("SQL_INS_INVESTMENTSTAT"), m_sRaceDate, m_sVenue, i.ToString(), out batchno);
            }
            try
            {
                lock (SHARED_LOCK)
                {
                    OrclFunc.ExecNonQueryTransLocal(sqls, sqlcount);
                }
            }
            catch (Exception e)
            {
                LogMsg("WriteAllInvestmentStatToDB error:"+e.Message);
                return;
            }
            LogMsg("All races investment stat saved");
        }

        private TResult WriteDoubleOddsToDB(string sOdds, int raceno1, int raceno2,string inv_batchno,string wp_batchno,string inv_batchno2,string wp_batchno2)
        {
            TResult rt = new TResult();
            string batchno = GetRefreshTimeTag(sOdds);
            DateTime dt;

            if (!DateTime.TryParseExact(batchno, "MMddHHmmss", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out dt))
            {
                rt.ErrMsg = "WriteDoubleOddsToDB->GetRefreshTimeTag failed,sOdds=" + sOdds.Substring(0, sOdds.Length > 20 ? 20 : sOdds.Length);
                rt.Success = false;
                return rt;
            }

            int i = 0;

            if (CheckBatchNoExists(m_sRaceDate, m_sVenue, raceno1.ToString(), "dbl_1st_batchno", batchno))
            {
                rt.Success = false;
                rt.ErrMsg = "No update for double odds of raceno #" + raceno1.ToString() + " at this time.";
                return rt;
            }

            string[] sqls = new string[200];
            string sql = string.Empty;
            string sqlpattern = LoadSql("SQL_INS_DOUBLE_ODDS");

            string[] ps = sOdds.Split(new string[] { "@@@;" }, StringSplitOptions.None);
            string[] ps1 = ps[1].Split(new string[] { ";","=" }, StringSplitOptions.None);

            
            int sqlcount = 0;
            //ps1[0] => horseno1-horseno2  ;  ps1[1] => odds ;ps1[2] => hotflag
            try
            {
                for (i = 0; i <= ps1.GetUpperBound(0); i++)
                {
                    string[] ps2 = ps1[i].Split(new string[] { "-" }, StringSplitOptions.None);
                    if (ps1[i + 1].ToUpper() == "SCR")
                        ps1[i + 1] = "0";

                    if (ps1[i + 1] != "0")
                    {
                        sqls[sqlcount] = string.Format(sqlpattern, batchno, m_sRaceDate, m_sVenue, raceno1.ToString(), raceno2.ToString(), ps2[0], ps2[1], ps1[i + 1], DateTime.Now.ToString("yyyyMMdd HH:mm:ss"), m_double_LastBatchNo[raceno1], inv_batchno, wp_batchno,  wp_batchno2);
                        sqlcount++;
                    }
                    i++;
                    i++;
                }
                lock (SHARED_LOCK)
                {
                    OrclFunc.ExecNonQueryTransLocal(sqls, sqlcount);
                }
            }
            catch (Exception e1)
            {
                rt.ErrMsg = "WriteDoubleOddsToDB error:raceno=(" + raceno1.ToString() + ","+raceno2.ToString() + ") msg=" + e1.Message;
                rt.Success = false;
            }
            //LogMsg("raceno=(" + raceno1 + "," + raceno2 + ") double odds stat saved");
            return rt;
        }        

        private void timer1_Tick(object sender, EventArgs e)
        {
            //
            timer1.Enabled = false;
            LogMsg("the " + (++m_iTickCount) + "th run of refreshing procedure begins...");

            //start backgroundworker
            m_bgw1.RunWorkerAsync("1-" + m_iRaceCount.ToString());
            //m_bgw2.RunWorkerAsync((m_iRaceCount / 2+1).ToString() + "-" + m_iRaceCount.ToString());
        }

        private void button2_Click(object sender, EventArgs e)
        {        
            InitData();                         
            
            if (timer1.Enabled)
            {
                timer1.Stop();
                button2.Text = "启动";
            }
            else
            {
                timer1.Start();
                button2.Text = "暂停";
                m_iTickCount = 0;
                timer1_Tick(null, null);                
            }
        }

        private void InitData()
        {
            if (string.IsNullOrEmpty(dtPicker1.Text) || string.IsNullOrEmpty(cbVenue.Text) || string.IsNullOrEmpty(cbRaceCount.Text))
            {
                MessageBox.Show("未选择日期、场地、场数");
                return;
            }

            m_sRaceDate = dtPicker1.Text;
            m_sVenue = cbVenue.Text;
            m_iRaceCount = int.Parse(cbRaceCount.Text);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            InitData();   
        }

        private void bgw1_DoWork(object sender, DoWorkEventArgs e)
        {
            string qin_url = "http://bet.hkjc.com/racing/getXML.aspx?type=jcbwracing_full&date={0}&venue={1}&raceno={2}&pool=qin&tag=QIN/RACE";
            string s_qin_odds = string.Empty;
            string qpl_url = "http://bet.hkjc.com/racing/getXML.aspx?type=jcbwracing_full&date={0}&venue={1}&raceno={2}&pool=qpl&tag=QPL/RACE";
            string s_qpl_odds = string.Empty;

            string double_url = "http://bet.hkjc.com/racing/getXML.aspx?type=jcbwracing_full&pool=dbl&date={0}&venue={1}&raceno={2}&tag=DBL/RACE"; //upper table            

            string racedate = m_sRaceDate.Substring(6) + "-" + m_sRaceDate.Substring(4, 2) + "-" + m_sRaceDate.Substring(0, 4);
            string s_double_odds = WebFunctions.GetXmlContent(string.Format(double_url, racedate, m_sVenue, 4));
            //WriteDoubleOddsToDB(s_double_odds, 4, 4 + 1);
        }

        private void button4_Click(object sender, EventArgs e)
        {
            GetRaceInfo2("", "", "");
        }

        public static string LoadSql(string sqlname)
        {
            string sql = string.Empty;
            lock (SHARED_LOCK)
            {
                sql = OrclFunc.ExecuteScalarStrLocal(string.Format("select t.str||t.str2 from t_string_base t where t.id = '{0}'", sqlname));
            }
            return sql;
        }

        private void ThreadWork(int raceno_start, int raceno_end,BackgroundWorker bw)
        {
            //int MinutesBeforeRace = 40;
            //int MinutesAfterRace = 5;
            TResult rt = new TResult();
            int i;
            string sql = string.Empty;
            string sqlpattern = string.Empty;
            string s_wp_odds = string.Empty;
            string s_qin_odds = string.Empty;
            string s_qpl_odds = string.Empty;
            string s_double_odds = string.Empty;

            //CAUTION: date format in url is 'DD-MM-YYYY'
            string wp_url = "http://bet.hkjc.com/racing/getXML.aspx?type=jcbwracing_winplaodds&date={0}&venue={1}&start={2}&end={3}";
            string qin_url = "http://bet.hkjc.com/racing/getXML.aspx?type=jcbwracing_full&date={0}&venue={1}&raceno={2}&pool=qin&tag=QIN/RACE";
            string qpl_url = "http://bet.hkjc.com/racing/getXML.aspx?type=jcbwracing_full&date={0}&venue={1}&raceno={2}&pool=qpl&tag=QPL/RACE";
            string double_url = "http://bet.hkjc.com/racing/getXML.aspx?type=jcbwracing_full&pool=dbl&date={0}&venue={1}&raceno={2}&tag=DBL/RACE"; //upper table
            //string investment_url = "http://bet.hkjc.com/racing/getXML.aspx?type=pooltot&date={0}&venue={1}&raceno={2}";            
            string racedate = m_sRaceDate.Substring(6) + "-" + m_sRaceDate.Substring(4, 2) + "-" + m_sRaceDate.Substring(0, 4);

            for (i = raceno_start; i <= raceno_end; i++)
            {
                //investment
                //rt = WriteInvestmentStatToDB(i);
                //if (rt.Success)
                //    bw.ReportProgress(0, "raceno=" + i + " investment stat saved");
                //else
                //    bw.ReportProgress(0, rt.ErrMsg);
                //wp 
                rt = WriteInvestmentStatToDB(i);
                if (rt.Success)
                {
                    bw.ReportProgress(0, "raceno=" + i + " investment stat saved");    
                }
                else
                {
                    bw.ReportProgress(0, rt.ErrMsg);
                }

                s_wp_odds = WebFunctions.GetXmlContent(string.Format(wp_url, racedate, m_sVenue, i, i));
                rt = WriteWpOddsToDB(
                    s_wp_odds,
                    i,
                    i,
                    m_inv_lastbachno[i]);
                if (rt.Success)
                {
                    bw.ReportProgress(0, "raceno=" + i + " winplace odds saved");                    
                }
                else
                    bw.ReportProgress(0, rt.ErrMsg);
            }
            for (i = raceno_start; i <= raceno_end; i++)
            {
                //qin pla                
                s_qin_odds = WebFunctions.GetXmlContent(string.Format(qin_url, racedate, m_sVenue, i));
                
                rt = WriteQpOddsToDB(
                    s_qin_odds,
                    WebFunctions.GetXmlContent(string.Format(qpl_url, racedate, m_sVenue, i)),
                    i,
                    m_inv_lastbachno[i],
                    m_wp_LastBatchNo[i]);

                if (rt.Success)
                {
                    bw.ReportProgress(0, "raceno=" + i + " qinplace odds saved");                    
                }
                else
                    bw.ReportProgress(0, rt.ErrMsg);
            
                //double
                if (i < m_iRaceCount)
                {
                    s_double_odds = WebFunctions.GetXmlContent(string.Format(double_url, racedate, m_sVenue, i));
                    rt = WriteDoubleOddsToDB(
                        s_double_odds,
                        i,
                        i + 1,
                        m_inv_lastbachno[i],
                        m_wp_LastBatchNo[i],
                        m_inv_lastbachno[i+1],
                        m_wp_LastBatchNo[i + 1]);
                    if (rt.Success)
                        bw.ReportProgress(0, "raceno=" + i + " double odds saved");                        
                    else
                        bw.ReportProgress(0, rt.ErrMsg);
                }
            }

        }

        private void m_bgw1_DoWork(object sender, DoWorkEventArgs e)
        {
            string raceno_interval = e.Argument as string;
            string[] ps = raceno_interval.Split(new string[] { "-" }, StringSplitOptions.None);
            ThreadWork(int.Parse(ps[0]), int.Parse(ps[1]),sender as BackgroundWorker);
        }

        private void m_bgw1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            LogMsg(e.UserState as string);
        }

        private void m_bgw1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (m_bgw1.IsBusy || m_bgw2.IsBusy || button2.Text != "暂停")
                return;

            //更新批号
            lock (SHARED_LOCK)
            {                
                string sql1 = LoadSql("UpdWpBatchno");//UpdQplBatchno  UpdDblBatchno
                string sql2 = LoadSql("UpdQplBatchno");//UpdQplBatchno  UpdDblBatchno
                string sql3 = LoadSql("UpdDblBatchno");//UpdQplBatchno  UpdDblBatchno

                OrclFunc.ExecuteNonQueryLocal(string.Format(sql1, m_sRaceDate, m_sVenue));
                OrclFunc.ExecuteNonQueryLocal(string.Format(sql2, m_sRaceDate, m_sVenue));
                OrclFunc.ExecuteNonQueryLocal(string.Format(sql3, m_sRaceDate, m_sVenue));
                
                LogMsg("已同步数据");
            }

            timer1.Enabled = true;
        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            InitData();
            for (int i = 1; i <= m_iRaceCount; i++)
            {
                GetRaceInfo2(m_sRaceDate, m_sVenue, i.ToString());
                LogMsg("Downloaded the " + i.ToString() + "th race & horse info");
            }
        }
    }
}
